<?php
error_reporting( E_ALL&~E_NOTICE );
require_once 'library/PHPExcel.php';
require_once 'library/PHPExcel/Writer/Excel2007.php';
require_once 'library/PHPExcel/Writer/Excel5.php';
require_once 'library/PHPExcel/Reader/Excel2007.php';
require_once 'library/PHPExcel/Reader/Excel5.php';
include_once 'library/PHPExcel/IOFactory.php';
set_time_limit(1800) ;
ini_set('memory_limit','700M');


  $_conn=mysql_connect('localhost','root','root') or die(mysql_errno());
   mysql_select_db('mydemo',$_conn) or die(mysql_errno());
   mysql_query("SET NAMES UTF8") or die(mysql_errno());


$filePath = dirname(__FILE__).'\test.xls';
if (!file_exists($filePath)) {
	exit("找不到");
}
$temp = array();
$data1 =array();
$value = "";
$arr = readexcel($filePath);
$flag = 1;
foreach ($arr as $key => $v) {
  $v = array_filter($v);
  //把英文括号换成中文括号
  $a = str_replace('(', '（', trim($v['公司名称']));
  $b = str_replace(')','）',$a);
  if($b != ""){
   $value .= @(empty($value)?"('公司名称'),":',')."('".$b."')"; 
  }
}
$sql = "INSERT INTO demo_copy (company_name) VALUES".$value; 
$res= mysql_query($sql);
if($res){
  echo $flag ;
  $flag++;
}else{
  echo 0;
}
/*$list = page_array($page_size,$page,$name,0);
for($i=0;$i<count($list);$i++){
  @$data1 = getcontent($list[$i]);
}
if(empty($data1)){
@$temp.array_push($data1);
}*/
/*print_r($list);*/
  //echo '<a href="./1234.php?page='.$page.'>点击执行</a>';



function readexcel( $filePath) {
 $objPHPExcel = PHPExcel_IOFactory::load($filePath);
$sheet = $objPHPExcel->getSheet(0);

//获取行数与列数,注意列数需要转换
$highestRowNum = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestColumn();
$highestColumnNum = PHPExcel_Cell::columnIndexFromString($highestColumn);

//取得字段，这里测试表格中的第一行为数据的字段，因此先取出用来作后面数组的键名
$filed = array();
for($i=0; $i<$highestColumnNum;$i++){
  $cellName = PHPExcel_Cell::stringFromColumnIndex($i).'1';
  $cellVal = $sheet->getCell($cellName)->getValue();//取得列内容
  $filed []= $cellVal;
}

//开始取出数据并存入数组
$data = array();
for($i=2;$i<=$highestRowNum;$i++){//ignore row 1
  $row = array();
  for($j=0; $j<$highestColumnNum;$j++){
    $cellName = PHPExcel_Cell::stringFromColumnIndex($j).$i;
    $cellVal = $sheet->getCell($cellName)->getValue();
    $row[ $filed[$j] ] = $cellVal;
  }
  $data []= $row;
}

return($data);
}



/**
 * 数组分页函数 核心函数 array_slice
 * 用此函数之前要先将数据库里面的所有数据按一定的顺序查询出来存入数组中
 * $count  每页多少条数据
 * $page  当前第几页
 * $array  查询出来的所有数组
 * order 0 - 不变   1- 反序
 */
function page_array($count,$page,$array,$order){
  global $countpage; #定全局变量
  $page=(empty($page))?'1':$page; #判断当前页面是否为空 如果为空就表示为第一页面 
    $start=($page-1)*$count; #计算每次分页的开始位置
  if($order==1){
   $array=array_reverse($array);
  }  
  $totals=count($array); 
  $countpage=ceil($totals/$count); #计算总页面数
  $pagedata=array();
 $pagedata=array_slice($array,$start,$count);
  return $pagedata; #返回查询数据
}
/**
 * 分页及显示函数
 * $countpage 全局变量，照写
 * $url 当前url
 */
/*function show_array($countpage,$url){
   $page=empty($_GET['page'])?1:$_GET['page'];
 if($page > 1){
   $uppage=$page-1;
 }else{
  $uppage=1;
 }
 if($page < $countpage){
   $nextpage=$page+1;
 
 }else{
   $nextpage=$countpage;
 }
    $str='<div style="border:1px; width:300px; height:30px; color:#9999CC">';
 $str.="<span>共 {$countpage} 页 / 第 {$page} 页</span>";
 $str.="<span><a href='$url?page=1'>  首页 </a></span>";
 $str.="<span><a href='$url?page={$uppage}'> 上一页 </a></span>";
 $str.="<span><a href='$url?page={$nextpage}'>下一页 </a></span>";
 $str.="<span><a href='$url?page={$countpage}'>尾页 </a></span>";
 $str.='</div>';
 return $str;
}*/






function getcontent($name){
$url="http://www.qichacha.com/search?key=$name";
/*$info=file_get_contents($url);
preg_match('|<table>(.*?)<\/table>|i',$info,$m);
print_r($m);*/
$data=array();
$content=file_get_contents($url);
 $mode = "/<span class=\"font-15 text-black m-r\">([^<]+)<\/span>/";
if(preg_match_all($mode,$content,$arr)){
  $data['name']=$arr[0][0]; 
 }


   $mode = "/<span><i class=\"i i-phone3\"><\/i>([^<]+)<\/span>/";
if(preg_match_all($mode,$content,$arr)){
  $data['phone']=$arr[0][0]; 
 }

   $mode = "/<span class=\"m-l\"><i class=\"fa  fa-envelope-o\"><\/i>([^<]+)<\/span>/";
if(preg_match_all($mode,$content,$arr)){
  $data['address']=$arr[0][0]; 
 }
 print_r($data);
 }
//生成Excel输出数据格式
/*$title =array("序号","微信名称","跟单数量","收货信息","发货状态");
 
$fileName = "test_excel";
getExcel($fileName,$title,$content2);*/

/*function getExcel($fileName,$headArr,$data){

    if(empty($data) || !is_array($data)){
        die("data must be a array");
    }
    if(empty($fileName)){
        exit;
    }


    //$excelType = PHPExcel_IOFactory::identify('./demo.xls');
    $objReader = PHPExcel_IOFactory::createReader('Excel5');
    $objPHPExcel = $objReader->load("./demo.xls"); 


    $date = date("Y_m_d",time());
    $fileName .= "_{$date}.xls";*/
 
    //创建新的PHPExcel对象
    //$objPHPExcel = new PHPExcel();
    //$objProps = $objPHPExcel->getProperties();
     
    //设置表头
    /** $key = ord("A");
    foreach($headArr as $v){
        $colum = chr($key);
        $objPHPExcel->setActiveSheetIndex(0) ->setCellValue($colum.'1', $v);
        $key += 1;
    }**/
/*         $field = array(
      2=>ord("C"),
      2=>ord("J"),
      4=>ord("D"),
      4=>ord("E"),
      5=>ord("F")
      );*/
/*    $column = 5;
    $objActSheet = $objPHPExcel->getActiveSheet();
    foreach($data as $key => $rows){ //行写入
        //$span = ord("A");
        foreach($rows as $keyName=>$value){// 列写入
            if($keyName==2){
             
                $j = chr(ord("J"));
               $objActSheet->setCellValue($j.$column, $value);
            }else if($keyName ==3){
                $j = chr(ord("C"));
               $objActSheet->setCellValue($j.$column, $value);
            }else if($keyName ==4 ){
                $j = chr(ord("D"));
               $objActSheet->setCellValue($j.$column, $value);
                $j = chr(ord("E"));
               $objActSheet->setCellValue($j.$column, $value);
            }else if($keyName == 5){
                 $j = chr(ord("F"));
               $objActSheet->setCellValue($j.$column, $value);
            }
            
             //j = chr($field[$keyName]);
             
            //  $span++;
            
        }
        $column++;
    }*/
 
/*    $fileName = iconv("utf-8", "gb2312", $fileName);
    //重命名表
    //$objPHPExcel->getActiveSheet()->setTitle('Simple');
    //设置活动单指数到第一个表,所以Excel打开这是第一个表
    $objPHPExcel->setActiveSheetIndex(0);
    //将输出重定向到一个客户端web浏览器(Excel2007)
      header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
      header("Content-Disposition: attachment; filename=\"$fileName\"");
      header('Cache-Control: max-age=0');
      $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');  
      $objWriter->save('php://output');  
      exit;
 
}*/
?>

